import sqlite3
import json
# 连接到SQLite数据库（如果不存在，则会自动创建）
conn = sqlite3.connect('video_database.db')

# 创建游标对象
c = conn.cursor()

# 创建视频表
c.execute('''CREATE TABLE IF NOT EXISTS videos
             (id INTEGER PRIMARY KEY AUTOINCREMENT,
              name TEXT,
              tags TEXT,
              storage_path TEXT,
              nodejson TEXT,
              edgejson TEXT,
              treejson TEXT,
              content TEXT)''')

# 创建联查表
c.execute('''CREATE TABLE IF NOT EXISTS link
             (node TEXT,
              video_id INTEGER,
              FOREIGN KEY(video_id) REFERENCES videos(id))''')



# 提交更改并关闭连接
conn.commit()
conn.close()

def clear_table(table_name):
    # 连接到SQLite数据库
    conn = sqlite3.connect('video_database.db')
    c = conn.cursor()

    # 执行SQL命令清空表格
    c.execute(f"DELETE FROM {table_name}")

    # 提交更改并关闭连接
    conn.commit()
    conn.close()


def show_table(table_name):
    # 连接到SQLite数据库
    conn = sqlite3.connect('video_database.db')
    c = conn.cursor()

    # 执行SQL命令检索表格内容
    c.execute(f"SELECT * FROM {table_name}")
    rows = c.fetchall()

    # 打印表格内容
    print(f"Contents of table '{table_name}':")
    for row in rows:
        print(row)

    # 关闭连接
    conn.close()



def insert_data(table_name, data):
    try:
        # 连接到SQLite数据库
        conn = sqlite3.connect('video_database.db')
        c = conn.cursor()

        # 检查数据是否已经存在
        c.execute(f"SELECT name FROM {table_name} WHERE name=?", (data[0],))
        existing_data = c.fetchone()

        # 如果数据不存在，则插入数据
        if not existing_data:
            c.execute(f"INSERT INTO {table_name} (name, tags, storage_path, nodejson, edgejson, treejson, content) VALUES (?, ?, ?, ?, ?, ?, ?)", data)
            # 提交更改
            conn.commit()
            print("Data inserted successfully!")
        else:
            print("Data already exists, skipping insertion.")

        # 关闭连接
        conn.close()
    except Exception as e:
        print("Error inserting data:", e)


#向联查表插入数据
def insert_data_to_link(nodejson, video_id):
    try:
        # 连接到SQLite数据库
        conn = sqlite3.connect('video_database.db')
        c = conn.cursor()

        # 解析nodejson字符串为JSON对象
        nodes = json.loads(nodejson)

        # 遍历所有节点
        for node in nodes:
            label = node['label']
            # 查询节点是否已存在
            c.execute("SELECT * FROM link WHERE node=?", (label,))
            existing_node = c.fetchone()
            if existing_node:
                # 如果节点已存在，更新视频编号列表
                existing_video_ids = json.loads(existing_node[1])
                if video_id not in existing_video_ids:
                    existing_video_ids.append(video_id)
                    c.execute("UPDATE link SET video_id=? WHERE node=?", (json.dumps(existing_video_ids), label))
            else:
                # 如果节点不存在，插入新节点
                c.execute("INSERT INTO link VALUES (?, ?)", (label, json.dumps([video_id])))

        # 提交更改并关闭连接
        conn.commit()
        conn.close()
        print("Data inserted successfully!")
    except Exception as e:
        print("Error inserting data:", e)

#按照视频搜索所有对应数据
def get_video_data(video_name):
    try:
        # 连接到SQLite数据库
        conn = sqlite3.connect('video_database.db')
        c = conn.cursor()

        # 查询视频数据
        c.execute("SELECT * FROM videos WHERE name=?", (video_name,))
        video_data = c.fetchone()

        # 关闭连接
        conn.close()

        # 如果找到对应视频，则返回数据
        if video_data:
            return {
                "id": video_data[0],
                "name": video_data[1],
                "tags": video_data[2],
                "storage_path": video_data[3],
                "nodejson": video_data[4],
                "edgejson": video_data[5],
                "treejson": video_data[6],
                "content": video_data[7]
            }
        else:
            print("Video not found!")
            return None
    except Exception as e:
        print("Error getting video data:", e)
        return None

#联查：输入node返回查到的视频id(list)
def get_video_ids_by_node(node_content):
    try:
        # 连接到 SQLite 数据库
        conn = sqlite3.connect('video_database.db')
        c = conn.cursor()

        # 查询节点对应的视频 ID
        c.execute("SELECT video_id FROM link WHERE node=?", (node_content,))
        video_ids = c.fetchone()
        #输出找到的id列表
        # print(video_ids)
        # 关闭连接
        conn.close()

        # 如果找到对应视频 ID，则返回视频 ID
        if video_ids:
            return json.loads(video_ids[0])
        else:
            print("No videos found for the node:", node_content)
            return None

    except Exception as e:
        print("Error getting video IDs by node:", e)
        return None

#输入视频id（list）返回全部内容
def get_videos_data_by_ids(video_ids):
    try:
        # 连接到 SQLite 数据库
        conn = sqlite3.connect('video_database.db')
        c = conn.cursor()

        # 查询视频数据
        videos_data = []
        for video_id in video_ids:
            c.execute("SELECT * FROM videos WHERE id=?", (video_id,))
            video_data = c.fetchone()
            if video_data:
                videos_data.append({
                    "id": video_data[0],
                    "name": video_data[1],
                    "tags": video_data[2],
                    "storage_path": video_data[3],
                    "nodejson": video_data[4],
                    "edgejson": video_data[5],
                    "treejson": video_data[6],
                    "content": video_data[7]
                })

        # 关闭连接
        conn.close()

        return videos_data
    except Exception as e:
        print("Error getting videos data by IDs:", e)
        return None

# #注入数据
# # 打开文件
# with open('node.txt', 'r') as file:
#     # 读取文件内容
#     node = file.read()

# with open('edge.txt', 'r') as file:
#     # 读取文件内容
#     edge = file.read()

# with open('tree.txt', 'r') as file:
#     # 读取文件内容
#     tree = file.read()
# # # 调用函数插入数据
# data_to_insert = ('太阳活动对人造天体的影响', '地理', '/path/to/video3', node, edge, tree, 'Sample content for Video 3')

# insert_data('videos', data_to_insert)

# 调用函数显示指定表格的内容
# show_table('videos')


# video_id = 4
# insert_data_to_link(node, video_id)

show_table('link')

# video_ids = get_video_data_by_node("没有")
# if video_ids:
#     print("Videos found for the node '没有':", video_ids)
# else:
#     print("No videos found for the node '没有'")



# 调用函数输出 link 表中节点对应的视频 ID
node_content = "能量"
video_ids = get_videos_data_by_ids(get_video_ids_by_node(node_content))
print(video_ids)
